/**
 *    Copyright 2012 Geensoft S.A.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 **/
package ar.com.greensoft.neo.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import ar.com.greensoft.neo.dao.DaoException;
import ar.com.greensoft.neo.dao.ProvinciaDao;
import ar.com.greensoft.neo.model.ObraSocial;
import ar.com.greensoft.neo.model.Provincia;
import ar.com.greensoft.neo.service.ImportService;
import ar.com.greensoft.neo.util.SelectEntity;

/**
 * @author Jorge E. Villaverde
 *
 */
@Service
public class ExcelImportServiceImpl implements ImportService {
	private static final int RNOS_CELL_NUM = 0;
	private static final int DENOMINACION_CELL_NUM = 1;
	private static final int SIGLA_CELL_NUM = 2;
	private static final int DOMICILIO_CELL_NUM = 3;
	private static final int LOCALIDAD_CELL_NUM = 4;
	private static final int CP_CELL_NUM = 5;
	private static final int PROVINCIA_CELL_NUM = 6;
	private static final int TELEFONO1_CELL_NUM = 7;
	private static final int TELEFONO2_CELL_NUM = 8;
	private static final int EMAIL_CELL_NUM = 9;
	private static final int WEB_CELL_NUM = 10;

	private Logger logger = LoggerFactory.getLogger(ExcelImportServiceImpl.class);
	
	@Autowired
	private ProvinciaDao provinciaDao;
	
	@Override
	public List<SelectEntity<ObraSocial>> importObraSociales(InputStream is) {
		List<SelectEntity<ObraSocial>> ret = null;
	
		try {
			HSSFWorkbook wb = new HSSFWorkbook(is);
			
			HSSFSheet sheet = wb.getSheetAt(0);
			
			ret = importFromSheet(sheet);			
		} catch (IOException e) {
			logger.error(e.getMessage());
		}
		
		return ret;
	}

	private List<SelectEntity<ObraSocial>> importFromSheet(HSSFSheet sheet) {
		List<SelectEntity<ObraSocial>> ret = new ArrayList<SelectEntity<ObraSocial>>();
		
		int firstRow = sheet.getFirstRowNum();
		int lastRow = sheet.getLastRowNum();
		
		for(int i = firstRow+1; i < lastRow; i++){
			HSSFRow row = sheet.getRow(i);
			
			int rnos =  getCellIntValue(row.getCell(RNOS_CELL_NUM));	
			String denominacion = getCellStringValue(row.getCell(DENOMINACION_CELL_NUM));
			String sigla = getCellStringValue(row.getCell(SIGLA_CELL_NUM));
			String domicilio = getCellStringValue(row.getCell(DOMICILIO_CELL_NUM));
			String localidad = getCellStringValue(row.getCell(LOCALIDAD_CELL_NUM));
			String cp = getCellStringValue(row.getCell(CP_CELL_NUM));
			String provinciaNombre = getCellStringValue(row.getCell(PROVINCIA_CELL_NUM));
			String telefono1 = getCellStringValue(row.getCell(TELEFONO1_CELL_NUM));
			String telefono2 = getCellStringValue(row.getCell(TELEFONO2_CELL_NUM));
			String email = getCellStringValue(row.getCell(EMAIL_CELL_NUM));
			String web = getCellStringValue(row.getCell(WEB_CELL_NUM));
			
			Provincia provincia = null;
			if(StringUtils.hasText(provinciaNombre)){
				List<Provincia> l;
				try {
					l = provinciaDao.findLikeName(provinciaNombre);
					if(l != null && l.size() > 0)
						provincia = l.get(0);
				} catch (DaoException e) {
				}
			}

			ObraSocial os = new ObraSocial();
			os.setRnos(rnos);
			os.setName(denominacion);
			os.setSigla(sigla);
			os.getDireccion().setCalle(domicilio);
			os.getDireccion().setLocalidad(localidad);
			os.getDireccion().setCodigoPostal(cp);
			os.getDireccion().setProvincia(provincia);
			os.setTelefono1(telefono1);
			os.setTelefono2(telefono2);
			os.setEmail(email);
			os.setWeb(web);
			
			ret.add(new SelectEntity<ObraSocial>(os));
		}
		
		return ret;
	}
	
	private static int getCellIntValue(HSSFCell cell){
		if(cell != null){
			try{
				return (int) cell.getNumericCellValue();
			}catch(java.lang.IllegalStateException e){
			}
		}
		return 0;
	}

	private static String getCellStringValue(HSSFCell cell){
		if(cell != null){
			try{
				return cell.getStringCellValue().trim();
			}catch(java.lang.IllegalStateException e){
			}
		}
		return "";
	}
}
